import json
from flask import Flask, request, jsonify, Response
from datetime import datetime
from flask_cors import CORS
from conn import connect_to_oracle
import traceback

from warningAPI import convert_value, transform_photo_urls

app = Flask(__name__)
CORS(app)  # 启用跨域支持


def get_db_connection():
    return connect_to_oracle()


@app.route('/api/boat', methods=['POST'])
def get_boat():
    try:
        # 获取 POST 请求中的 JSON 数据
        data = request.get_json()
        # 从 JSON 中提取所需的参数
        boat_name = data.get('boatName')
        start_time = data.get('startTime')
        end_time = data.get('endTime')

        # 检查必要参数是否存在
        if not boat_name or not start_time or not end_time:
            return jsonify({"error": "缺少必要参数，必须传入 boatName, startTime, endTime"}), 400

        # 验证时间格式是否正确（格式应为 YYYY-MM-DD HH:MM:SS）
        try:
            datetime.strptime(start_time, '%Y-%m-%d %H:%M:%S')
            datetime.strptime(end_time, '%Y-%m-%d %H:%M:%S')
        except ValueError:
            return jsonify({"error": "时间格式错误，正确格式为 YYYY-MM-DD HH:MM:SS"}), 400

        # 获取数据库连接和游标
        conn = connect_to_oracle()
        cursor = conn.cursor()

        # 构造 SQL 查询语句，使用位置绑定
        sql = """
        SELECT 
            d.CODE AS CODE,
            d.BOAT_NAME AS boatname,
            d.PASSTIME AS passtime,
            b.NAME AS port,
            oc.BOAT_TYPE AS boattype,
            d.LENGTH AS length,
            d.WIDTH AS width,
            d.HEIGHT AS height,
            d.SPEED AS speed,
            CASE 
                WHEN d.DIRECTION = 1 THEN '上行' 
                WHEN d.DIRECTION = 2 THEN '下行' 
                ELSE '未知' 
            END AS direction,
            CASE 
                WHEN d.LOAD = 0 THEN '空载' 
                WHEN d.LOAD = 1 THEN '重载' 
                ELSE '未知' 
            END AS load,
            wt.NAME AS warntype,
            w.WARNING_TIME AS warntime
        FROM 
            BAYONET_DYNAMIC.DATAFUSION d
        LEFT JOIN BAYONET_BASICS.SYS_BAYONET b 
            ON d.BAYONET_ID = b.ID
        LEFT JOIN BAYONET_BUSSINESS.BOAT_WARNING w 
            ON d.CODE = w.EVENTCODE
        LEFT JOIN BAYONET_BUSSINESS.ZD_WARNING_TYPE wt 
            ON w.WARNING_TYPE = wt.ID
        LEFT JOIN BAYONET_BUSSINESS.OFF_SITE_CASE oc 
            ON d.CODE = oc.EVENT_CODE
        WHERE 
            d.BOAT_NAME = ?
            AND d.PASSTIME BETWEEN 
                TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS') 
                AND TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS')
        """

        # 执行 SQL 查询，传入参数作为位置绑定
        cursor.execute(sql, (boat_name, start_time, end_time))

        # 获取查询结果的列名
        columns = [col[0] for col in cursor.description]
        # 构造结果列表
        result = []
        for row in cursor.fetchall():
            # 将查询结果转为字典，并转换键值为 Python 原生类型
            row_dict = dict(zip(columns, row))
            # 强制将所有键和值转换为 Python 的原生类型（字符串、整数、浮点数等）
            row_dict = {str(key): (str(value) if value is not None else None) for key, value in row_dict.items()}
            result.append(row_dict)

        # 关闭游标与数据库连接
        cursor.close()
        conn.close()

        response = {
            "result": result
        }

        # 使用jsonify并显式设置charset
        return Response(
            json.dumps(response, ensure_ascii=False),
            content_type='application/json; charset=utf-8'
        )

    except Exception as e:
        # 捕获异常并返回错误信息
        traceback_str = traceback.format_exc()
        return jsonify({"error": str(e), "trace": traceback_str}), 500


@app.route('/api/portflow', methods=['POST'])
def get_flow():
    data = request.get_json()

    required_params = ['bayonet_name', 'startTime', 'endTime']
    if not all(param in data for param in required_params):
        return jsonify({"error": "缺少必要的参数"}), 400

    bayonet_name = data['bayonet_name']
    start_time = data['startTime']
    end_time = data['endTime']

    try:
        # 确保格式正确
        start_time = datetime.strptime(start_time, '%Y-%m-%d %H:%M:%S').strftime('%Y-%m-%d %H:%M:%S')
        end_time = datetime.strptime(end_time, '%Y-%m-%d %H:%M:%S').strftime('%Y-%m-%d %H:%M:%S')
    except ValueError:
        return jsonify({"error": "时间格式错误，正确格式为 YYYY-MM-DD HH:MM:SS"}), 400

    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        query = """
        WITH date_range AS (
            SELECT TRUNC(TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS') + LEVEL - 1) AS pass_date
            FROM DUAL
            CONNECT BY LEVEL <= TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS') - TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS') + 1
        ),
        bayonet_info AS (
            SELECT id, name 
            FROM BAYONET_BASICS.SYS_BAYONET 
            WHERE name = ?
        )
        SELECT 
            ? AS bayonetName,
            dr.pass_date,
            COUNT(info.BOAT_NAME) AS total,
            COUNT(CASE WHEN info.DIRECTION = 1 THEN 1 END) AS outCount,
            COUNT(CASE WHEN info.DIRECTION = 2 THEN 1 END) AS inCount,
            COUNT(CASE WHEN mvw.SHIP_TYPE_NAME = '干货船' THEN 1 END) AS dryShip,
            COUNT(CASE WHEN mvw.SHIP_TYPE_NAME = '油船' THEN 1 END) AS oilShip,
            COUNT(CASE WHEN mvw.SHIP_TYPE_NAME = '散货船' THEN 1 END) AS bulkShip,
            COUNT(CASE WHEN mvw.SHIP_TYPE_NAME = '散装化学品船' THEN 1 END) AS bulkChemicalShip,
            COUNT(CASE WHEN mvw.SHIP_TYPE_NAME = '散装水泥运输船' THEN 1 END) AS bulkCementShip,
            COUNT(CASE WHEN mvw.SHIP_TYPE_NAME = '多用途船' THEN 1 END) AS multipurposeShip,
            COUNT(CASE 
                WHEN mvw.SHIP_TYPE_NAME NOT IN (
                    '干货船', '油船', '散货船', '散装化学品船', '散装水泥运输船', '多用途船'
                ) THEN 1 
            END) AS other,
            NVL(SUM(mvw.SHIP_GROSSTON), 0) AS zd
        FROM date_range dr
        CROSS JOIN bayonet_info sys
        LEFT JOIN bayonet_dynamic.DATAFUSION info 
            ON TRUNC(info.PASSTIME) = dr.pass_date
            AND info.BAYONET_ID = sys.id
            AND info.PASSTIME BETWEEN TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS') 
                                  AND TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS')
        LEFT JOIN GHJG_BASICS.MVW_BOAT_INFO mvw 
            ON mvw.SHIP_NAME_CN = info.BOAT_NAME
        GROUP BY dr.pass_date
        ORDER BY dr.pass_date ASC
        """

        cursor.execute(query, [start_time, end_time, start_time, bayonet_name, bayonet_name, start_time, end_time])
        result = cursor.fetchall()

        columns = [str(col[0]) for col in cursor.description]  # 保证是 Python 字符串类型

        data_result = []
        for row in result:
            clean_row = [str(val) if hasattr(val, 'toString') else val for val in row]
            data_result.append(dict(zip(columns, clean_row)))

        cursor.close()
        conn.close()

        response = {
            "result": data_result
        }

        # 使用jsonify并显式设置charset
        return Response(
            json.dumps(response, ensure_ascii=False),
            content_type='application/json; charset=utf-8'
        )

    except Exception as e:
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500


@app.route('/api/boatName', methods=['POST'])
def get_boat_info():
    try:
        data = request.get_json()
        boat_name = data.get("boatName")
        if not boat_name:
            return jsonify({"error": "缺少参数 boatName"}), 400

        query = """
            SELECT DISTINCT 
                mvw.SHIP_NAME_CN AS boatName,
                mvw.MMSI AS mmsi,
                mvw.SHIP_REG_NO AS regNo,
                mvw.REGPORT_NAME AS portName,
                DECODE(mvw.SHIP_REGION_TYPE, 0, '内河船', 1, '海船') AS shipRegionType,
                mvw.SHIP_TYPE_NAME AS shipTypeName,
                mvw.SHIP_LENGTH AS shipLength,
                mvw.SHIP_BREADTH AS shipWidth,
                mvw.SHIP_DEPTH AS shipDepth,
                mvw.SHIP_OWNER_CN AS owner,
                mvw.SHIP_BUILT_DATE AS buildTime,
                mvw.SHIP_ENGINE_POWER AS power,
                mvw.SHIP_GROSSTON AS zd
            FROM ghjg_basics.mvw_boat_info mvw
            WHERE mvw.SHIP_NAME_CN = ?
        """

        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute(query, (boat_name,))
        # 转换列名为小写字符串
        columns = [str(col[0]).lower() for col in cursor.description]
        # 转换每一行中的 Java 类型为 Python 原生

        def clean_row(row):
            return [str(item) if isinstance(item, object) and 'java.lang' in str(type(item)) else item for item in row]

        results = [dict(zip(columns, clean_row(row))) for row in cursor.fetchall()]
        # 如果结果只有一行，直接返回该对象，否则保持列表结构
        if len(results) == 1:
            results = results[0]

        cursor.close()
        conn.close()

        response = {
            "result": results
        }

        return Response(
            json.dumps(response, ensure_ascii=False),
            content_type='application/json; charset=utf-8'
        )

    except Exception as e:
        print("发生异常，详细堆栈如下：")
        traceback.print_exc()
        return jsonify({
            "error": "服务器内部错误",
            "details": str(e)
        }), 500

@app.route('/api/warning', methods=['POST'])
def get_warning():
    try:
        data = request.get_json()
        print("收到请求数据：", data)

        boat_name = data.get("boatName")
        start_time = data.get("startTime")
        end_time = data.get("endTime")

        if not boat_name or not start_time or not end_time:
            return jsonify({"error": "参数缺失，需包含 boatName、startTime、endTime"}), 400

        sql = """
        SELECT T.*
        FROM (
            SELECT TO_CHAR(warn.WARNCODE) warnCode,
                   warn.WARNING_TIME warnTime,
                   TO_CHAR(warn.BOAT_NAME) boatName,
                   TO_CHAR(type.NAME) warnType,
                   info.PASSTIME passTime,
                   info.BAYONET_ID bayonetId,
                   sys.name bayonetName,
                   video.PHOTONAME AS photoName,
                   NULL AS photoPosition,
                   CASE 
                       WHEN warn.warning_message_status = 1 THEN '已发送'
                       ELSE '未发送'
                   END AS msgSendStatus
            FROM bayonet_bussiness.boat_warning warn
            LEFT JOIN bayonet_dynamic.DATAFUSION info ON info.CODE = warn.EVENTCODE
            LEFT JOIN bayonet_bussiness.zd_warning_type type ON warn.WARNING_TYPE = type.id
            LEFT JOIN ghjg_basics.mvw_boat_info mvw ON info.BOAT_NAME = mvw.SHIP_NAME_CN
            LEFT JOIN BAYONET_BASICS.SYS_BAYONET sys ON sys.id = info.BAYONET_ID
            LEFT JOIN bayonet_bussiness.message_send_record m ON m.warning_code = warn.warncode
            LEFT JOIN BAYONET_DYNAMIC.VIDEO_ANALYSIS video ON video.EVENTCODE = info.CODE
            WHERE info.BAYONET_ID IS NOT NULL
              AND mvw.SHIP_TYPE_NAME IS NOT NULL

            UNION

            SELECT TO_CHAR(osc.id) warnCode,
                   osc.create_time warnTime,
                   TO_CHAR(info.boat_name) boatName,
                   DECODE(osc.type, 1, '国旗未悬挂', 2, '货物未覆盖') warnType,
                   info.passtime passTime,
                   info.BAYONET_ID bayonetId,
                   sys.name bayonetName,
                   NVL(video.PHOTONAME, '') || NVL(coverage.COLUMN1, '') AS photoName,
                   coverage.COLUMN1 AS photoPosition,
                   DECODE(osc.case_message_status, 0, '未发送', '已发送') msgSendStatus
            FROM bayonet_bussiness.off_site_case osc
            LEFT JOIN bayonet_dynamic.datafusion info ON osc.event_code = info.code
            LEFT JOIN BAYONET_BASICS.SYS_BAYONET sys ON sys.id = info.BAYONET_ID
            LEFT JOIN BAYONET_DYNAMIC.VIDEO_ANALYSIS video ON video.EVENTCODE = info.CODE
            LEFT JOIN BAYONET_DYNAMIC.COVERAGE_ANALYSIS coverage ON coverage.EVENT_CODE = info.CODE
        ) T
        WHERE T.boatName = ?
          AND T.warnTime BETWEEN TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS')
                             AND TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS')
        ORDER BY T.warnTime DESC
        """

        conn = connect_to_oracle()
        curs = conn.cursor()
        curs.execute(sql, [boat_name, start_time, end_time])
        columns = [str(desc[0]).lower() for desc in curs.description]
        rows = curs.fetchall()

        result = []
        for row in rows:
            row_dict = {}
            for i, col in enumerate(columns):
                value = convert_value(row[i])
                if col == "photoname":
                    photo_name = value
                    bayonet_id = str(row[columns.index("bayonetid")])
                    warn_type = row[columns.index("warntype")] if "warntype" in columns else None
                    photo_position = row[columns.index("photoposition")] if "photoposition" in columns else None
                    row_dict[col] = transform_photo_urls(photo_name, bayonet_id, warn_type, photo_position)
                else:
                    row_dict[col] = value
            result.append(row_dict)

        curs.close()
        conn.close()

        response = {
            "result": result
        }

        return Response(
            json.dumps(response, ensure_ascii=False),
            content_type='application/json; charset=utf-8'
        )

    except Exception as e:
        traceback.print_exc()
        return jsonify({"error": "服务器内部错误", "details": str(e)}), 500

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000, debug=False)